package com.campus.dao.impl;

import com.campus.dao.UserDao;
import com.campus.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
@Slf4j
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Optional<User> findByLoginName(String loginName) {
        String sql = "SELECT * FROM t_sys_user WHERE login_name = ?";
        try {
            User user = jdbcTemplate.queryForObject(sql, new Object[]{loginName}, new RowMapper<User>() {
                @Override
                public User mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
                    return new User()
                            .setId(rs.getInt("id"))
                            .setLoginName(rs.getString("login_name"))
                            .setPassword(rs.getString("password"))
                            .setGender(rs.getString("gender"))
                            .setBirthday(rs.getDate("birthday"))
                            .setPhone(rs.getString("phone"))
                            .setEmail(rs.getString("email"))
                            .setStatus(rs.getString("status"));
                }
            });
            return Optional.ofNullable(user);
        } catch (Exception e) {
            log.error("Error fetching user by login name: " + e.getMessage(), e);
        }
        return Optional.empty();  // If user not found, return empty Optional
    }

    @Override
    public Optional<Integer> getRoleIdByUserId(Integer userId) {
        String sql = "SELECT role_id FROM t_sys_user_role WHERE user_id = ?";
        try {
            Integer roleId = jdbcTemplate.queryForObject(sql, new Object[]{userId}, Integer.class);
            return Optional.ofNullable(roleId);
        } catch (Exception e) {
            log.error("Error fetching role ID for user: " + e.getMessage(), e);
        }
        return Optional.empty();  // If no role found, return empty Optional
    }

    @Override
    public boolean addUser(User user, Integer roleId) {

        if(isValidLoginName(user.getLoginName())){
            return false;
        }

        if(!isValidRoleId(roleId)){
            return false;
        }

        String userSql = "INSERT INTO t_sys_user (login_name, password, gender, birthday, phone, email, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        String roleSql = "INSERT INTO t_sys_user_role (user_id, role_id) VALUES (?, ?)";

        try {
            // Insert user
            jdbcTemplate.update(userSql, user.getLoginName(), user.getPassword(), user.getGender(),
                    user.getBirthday() != null ? new java.sql.Date(user.getBirthday().getTime()) : null,
                    user.getPhone(), user.getEmail(), user.getStatus());

            // Retrieve the last inserted user ID
            Integer userId = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);

            // Bind user to role
            jdbcTemplate.update(roleSql, userId, roleId);
            return true;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public User getUserById(Integer id) {
        String sql = "SELECT * FROM t_sys_user WHERE id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, new RowMapper<User>() {
                @Override
                public User mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
                    return new User()
                            .setId(rs.getInt("id"))
                            .setLoginName(rs.getString("login_name"))
                            .setPassword(rs.getString("password"))
                            .setGender(rs.getString("gender"))
                            .setBirthday(rs.getDate("birthday"))
                            .setPhone(rs.getString("phone"))
                            .setEmail(rs.getString("email"))
                            .setStatus(rs.getString("status"));
                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;  // Return null if user not found
    }

    // Check if login_name is duplicate
    private boolean isValidLoginName(String loginName) {
        String sql = "SELECT COUNT(*) FROM t_sys_user WHERE login_name = ?";
        try {
            Integer count = jdbcTemplate.queryForObject(sql, new Object[]{loginName}, Integer.class);
            return count > 0;  // If count > 0, login name is duplicated
        } catch (EmptyResultDataAccessException e) {
            // No result found, login name is valid (not duplicated)
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;  // If there's an error, consider it invalid
        }
    }


    // Check if roleId is valid
    private boolean isValidRoleId(int roleId) {
        String sql = "SELECT COUNT(*) FROM t_sys_role WHERE id = ?";
        try {
            Integer count = jdbcTemplate.queryForObject(sql, new Object[]{roleId}, Integer.class);
            return count > 0;  // If count > 0, role ID is valid
        } catch (EmptyResultDataAccessException e) {
            // No result found, role ID is invalid
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;  // If there's an error, consider it invalid
        }
    }


    @Override
    public boolean bindUserRole(Integer userId, Integer roleId) {
        String sql = "INSERT INTO t_sys_user_role (user_id, role_id) VALUES (?, ?)";
        try {
            int rowsAffected = jdbcTemplate.update(sql, userId, roleId);
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;  // If insertion fails, return false
    }

    @Override
    public int updateUser(User user) {
        String sql = "UPDATE t_sys_user SET login_name = ?, password = ?, gender = ?, birthday = ?, phone = ?, email = ?, status = ? WHERE id = ?";
        try {
            return jdbcTemplate.update(sql,
                    user.getLoginName(),
                    user.getPassword(),
                    user.getGender(),
                    user.getBirthday(),
                    user.getPhone(),
                    user.getEmail(),
                    user.getStatus(),
                    user.getId());
        } catch (Exception e) {
            log.error("更新用户时出错: {}", e.getMessage(), e);
        }
        return 0;
    }

    @Override
    public int deleteUser(Integer loginId) {
        // 删除 t_sys_user_role 表中的数据
        String deleteUserRoleSql = "DELETE FROM t_sys_user_role WHERE user_id = ?";
        try {
            jdbcTemplate.update(deleteUserRoleSql, loginId);
        } catch (Exception e) {
            log.error("删除 t_sys_user_role 表中用户角色时出错: {}", e.getMessage(), e);
            return 0;
        }

        // 删除 t_manager_student 表中的数据
        String deleteStudentSql = "DELETE FROM t_manager_student WHERE login_id = ?";
        try {
            jdbcTemplate.update(deleteStudentSql, loginId);
        } catch (Exception e) {
            log.error("删除 t_manager_student 表中数据时出错: {}", e.getMessage(), e);
            return 0;
        }

        // 删除 t_manager_teacher 表中的数据
        String deleteTeacherSql = "DELETE FROM t_manager_teacher WHERE login_id = ?";
        try {
            jdbcTemplate.update(deleteTeacherSql, loginId);
        } catch (Exception e) {
            log.error("删除 t_manager_teacher 表中数据时出错: {}", e.getMessage(), e);
            return 0;
        }

        // 删除 t_sys_user 表中的数据
        String deleteUserSql = "DELETE FROM t_sys_user WHERE id = ?";
        try {
            return jdbcTemplate.update(deleteUserSql, loginId);
        } catch (Exception e) {
            log.error("删除 t_sys_user 表中用户时出错: {}", e.getMessage(), e);
        }

        return 0;
    }


    @Override
    public void updateUserToken(int userId, String token) {
        String sql = "UPDATE t_sys_user SET token = ? WHERE id = ?";
        jdbcTemplate.update(sql, token, userId);
    }

    @Override
    public Optional<String> getLoginNameByToken(String token) {
        String sql = "SELECT login_name FROM t_sys_user WHERE token = ?";
        String loginName = jdbcTemplate.queryForObject(sql, String.class, token);
        return Optional.ofNullable(loginName);
    }

    @Override
    public List<User> findAllUsers() {
        String sql = "SELECT * FROM t_sys_user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

}
